<?php
/*
 * Created on May 15, 2009
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
?>
<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

/**
 *  User_group Model Class
 *
 */

class  Product_model extends CI_Model
{
	/**
	 * @var unknown_type
	 */
	var $id  ;  
	/**
	 * @var unknown_type
	 */
	var $category_parent_id  ;  
	/**
	 * @var unknown_type
	 */
	var $name_vi  ; 
	/**
	 * @var unknown_type
	 */
	var $name_en  ;  
	/**
	 * @var unknown_type
	 */
	var $description_vi;
	/**
	 * @var unknown_type
	 */
	var $description_en;
	/**
	 * @var unknown_type
	 */
	var $picture;
	/**
	 * @var unknown_type
	 */
	var $active;
	/*
	 * @access	public
	 */

        function __construct() {
            parent::__construct();
        }

	function Product_model()
	{
            parent::__construct();
	}
	
	function create($product){
		//if(!is_array($products))
		//	$products = array($product);
		
		$sql = "INSERT INTO `gtb_product` " .
				"( `code`, `id_pcategory`, " .
				"`code_pcategory`, `vi_name`, `en_name`, `vi_description`, " .
				"`en_description`, `id_unit`, `id_source`, `id_provider`,`quantity`, " .
				"`price`, `special`,`best_sell`, `active`,`image`, `date_add`, `date_upd`) " .
				"VALUES ";
		//for($i = 0; $i < count($categories); $i++){
		//foreach($products as $product ){
			//$category = $categories[$i];
			$sql .= "(" .
					 $this->db->escape($product['code']).", " .
					 intval($product['id_pcategory']).", " .
					 $this->db->escape($product['code_pcategory']).", " .
					 $this->db->escape($product['vi_name']).", " .
					  $this->db->escape($product['en_name']).", " .
					 "'" . ($product['vi_description'])."', " .
					 "'" . ($product['en_description'])."', " .
					 intval($product['id_unit']).", " .
					 intval($product['id_source']).", " .
					 intval($product['id_provider']).", " .
					 intval($product['quantity']).", " .
					 $this->db->escape($product['price']).", " .
					 intval($product['special']).", " .
                     intval($product['best_sell']).", " .
					 intval($product['active']).", " .
					 $this->db->escape($product['image']).", " .
					 $this->db->escape($product['date_add']).", " .
					 $this->db->escape($product['date_upd'])."" .
					")";
				
		//}
        
		//$sql = substr($sql, 0, strlen($sql)-1);
		$this->db->query($sql);
		//return $this->db->affected_rows(); 
		return $this->db->insert_id(); 
		
	}
	
	function update_multiple($products){
		if(!is_array($products))
			$products = array($products);
		$id_products = array();
		$sql = "INSERT INTO `gtb_tmp_product` " .
				"( `code`, `id_pcategory`, " .
				"`code_pcategory`, `vi_name`, `en_name`, `vi_description`, " .
				"`en_description`, `id_unit`, `id_source`, `id_provider`,`quantity`, " .
				"`price`, `active`,`image`, `date_add`, `date_upd`) " .
				"VALUES ";
		//for($i = 0; $i < count($categories); $i++){
		foreach($products as $product ){
			//$category = $categories[$i];
			$sql .= "(" .
					 $this->db->escape($product->code).", " .
					 intval($product->id_pcategory).", " .
					 $this->db->escape($product->code_pcategory).", " .
					 $this->db->escape($product->vi_name).", " .
					  $this->db->escape($product->en_name).", " .
					 "'" . ($product->en_description)."', " .
					"'" . ($product->vi_description)."', " .
					 intval($product->id_unit).", " .
					 intval($product->id_source).", " .
					 intval($product->id_provider).", " .
					 intval($product->quantity).", " .
					 $this->db->escape($product->price).", " .
					 intval($product->active).", " .
					 $this->db->escape($product->image).", " .
					 $this->db->escape($product->date_add).", " .
					 $this->db->escape($product->date_upd)."" .
					"),";
			$code_products[] = $this->db->escape($product->code);
		}
		$sql = substr($sql, 0, strlen($sql)-1);
		$this->db->query($sql);
		if($this->db->affected_rows()> 0){
			$sql = "UPDATE gtb_product p
				INNER JOIN gtb_tmp_product t ON p.code = t.code
				SET ".
				"p.code = t.code, " . 
				"p.id_pcategory = t.id_pcategory, " .  
				"p.code_pcategory = t.code_pcategory, " . 
				"p.vi_name = t.vi_name, " . 
				"p.en_name = t.en_name, " .  
				"p.vi_description = t.vi_description, " .  
				"p.en_description = t.en_description, " . 
				"p.id_unit = t.id_unit, " .  
				"p.id_source = t.id_source, " .  
				"p.quantity = t.quantity, " .  
				"p.price = t.price, " .  
				"p.active = t.active, " .  
				"p.image = t.image, " .  
				"p.date_upd = t.date_upd " .  
				" WHERE p.code in ( " .implode(',', $code_products) . ')';
			
			$this->db->query($sql);
			
			$sql = "Delete from gtb_tmp_product";
			$this->db->query($sql);
			
			return $this->db->affected_rows();
		
		}
		return 0; 
		
	}
	
	function update($category){
		$sql = "UPDATE gtb_product
				SET 
				code = " .  $this->db->escape($category['code']) . ", " .
				"id_pcategory = " .  intval($category['id_pcategory']) . "," .
				"code_pcategory = " .  $this->db->escape($category['code_pcategory']) . ",".
				"vi_name = " .  $this->db->escape($category['vi_name']) . "," .
				"en_name = " .  $this->db->escape($category['vi_name']) . ", " .
				"vi_description = '" .  ($category['vi_description']) . "', " .
				"en_description = '" .  ($category['en_description']) . "', " .
				"id_unit = " .  intval($category['id_unit']) . ", " .
				"id_source = " .  intval($category['id_source']) . ", " .
				"id_provider = " .  intval($category['id_provider']) . ", " .
				"quantity = " .  intval($category['quantity']) . ", " .
				"price = " .  $this->db->escape($category['price']) . ", " .
				"old_price = " .  $this->db->escape($category['price']) . ", " .
				"sale_off = " .  $this->db->escape($category['sale_off']) . ", " .
				"active = " .  intval($category['active']) . ", " .
				"special = " .  intval($category['special']) . ", " .
                "best_sell = " .  intval($category['best_sell']) . ", " .
				"image = " .  $this->db->escape($category['image']) . ", " .
				"date_upd = " .  $this->db->escape($category['date_upd']) .
				" WHERE id_product = " .intval($category['id_product']);
		$this->db->query($sql);
		return $this->db->affected_rows(); 
	}
	
	function delete($id){
		if(!is_array($id)) $id = array($id);
		$ids = implode(',', $id);

        $sql = "SELECT image FROM gtb_product WHERE id_product IN ($ids)";
        $query = $this->db->query($sql);
		$result = $query->result();

		for($i = 0; $i < count($result); $i++){
			@unlink($result[$i]->image);
		}

        $sql = "SELECT file_url FROM gtb_fck_files WHERE item_id IN ($ids) AND type='product'";
        $query = $this->db->query($sql);
		$result = $query->result();

		for($i = 0; $i < count($result); $i++){
			@unlink($result[$i]->file_url);
		}
        
		$sql = "DELETE FROM gtb_product WHERE id_product IN ($ids)";
		//$sql = "DELETE FROM gtb_pcategory WHERE id_pcategory = " . intval($id);
		$this->db->query($sql);
		return $this->db->affected_rows(); 
	}
	
	function getAll($offset, $number_record, $id_category =  -1){
		$result = null;
		if(!is_array($id_category))
			$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, vi_description " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE  id_pcategory = " . intval($id_category) . " OR -1= " .intval($id_category) .
				" ORDER BY p.id_product DESC".
				" LIMIT $offset, $number_record";
		else 
			$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, vi_description " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE  id_pcategory IN (". implode(',',$id_category). ")" .
				" ORDER BY p.id_product DESC".
				" LIMIT $offset, $number_record";
		
		//$query = $this->db->query($sql);
		//$result = $query->result();
		//return $result;

                $query = $this->db->query($sql);
                //$result = $query->result_array();
                $result = $query->result_array();
                return $result;
	}
	
	function countAll($id_category =  -1){
		$result = null;	
		if(!is_array($id_category))
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				" WHERE  id_pcategory = " . intval($id_category) . " OR -1= " .intval($id_category);
		else
			$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				" WHERE  id_pcategory IN (". implode(',',$id_category). ")";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result[0]->num_record;
	}
	
	function search($offset, $number_record, $keyword, $id_category){
        $name =  $this->lang_code. '_name';
        $description =  $this->lang_code. '_description';
        if (!is_array($id_category))
        	$id_category = array($id_category);
        
        $id_category = implode(',', $id_category);
        	
		$sql = "SELECT p.*, p.$name name, u.$name unit, prov.$name provider, s.$name source, $description description, match(p.vi_name, p.en_name, p.vi_description, p.en_description, p.code_pcategory, p.code ) against(".$this->db->escape($keyword)." IN BOOLEAN MODE) as relevance " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
                " LEFT JOIN  gtb_provider prov ON prov.id_provider = p.id_provider" .
                " WHERE  (id_pcategory IN (". intval($id_category). ") OR (-1 = " . intval($id_category) . ")) AND  match(p.vi_name, p.en_name, p.vi_description, p.en_description, p.code_pcategory, p.code ) against(".$this->db->escape($keyword)." IN BOOLEAN MODE) " .
				" ORDER BY relevance DESC ".
				" LIMIT $offset, $number_record";
		$query = $this->db->query($sql);
		$result = $query->result_array();
       	
		return $result;
	}
	
	function countSearch($keyword, $id_category){
		if (!is_array($id_category))
        	$id_category = array($id_category);
        
        $id_category = implode(',', $id_category);
        
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				 " WHERE  (id_pcategory IN (". intval($id_category). ") OR (-1 = " . intval($id_category) . ")) AND   match(vi_name, en_name, vi_description, en_description, code_pcategory, code ) against(".$this->db->escape($keyword)." IN BOOLEAN MODE)";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	if($result)
			return $result[0]->num_record;
		else return 0;
	}
	
	/*
	 * $searchBy: field contain min and max
	 * 
	 * */
function searchMinMax($offset, $number_record, $searchBy, $min, $max, $id_category = -1){
        $name =  $this->lang_code. '_name';
        $description =  $this->lang_code. '_description';
        if (!is_array($id_category))
        	$id_category = array($id_category);
        
        $id_category = implode(',', $id_category);
        
        $condition = '';
        
        if ($min == 'less')
        	$condition = $searchBy . ' <= ' . doubleval($max);
        else if ($max = 'greater')
        	$condition = $searchBy . ' >= ' . doubleval($min);
        else 
        	$condition = $searchBy . ' >= ' . doubleval($min) . ' AND ' .  $searchBy . ' <= ' . doubleval($max);
        	
		$sql = "SELECT p.*, p.$name name, u.$name unit, prov.$name provider, s.$name source, $description description " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
                " LEFT JOIN  gtb_provider prov ON prov.id_provider = p.id_provider" .
                " WHERE  (id_pcategory IN (". intval($id_category). ") OR (-1 = " . intval($id_category) . ")) AND $condition " .
				" ORDER BY id_product DESC ".
				" LIMIT $offset, $number_record";
		$query = $this->db->query($sql);
		
		return $query->result_array();
       	
	}
	
	function countSearchMinMax($searchBy, $min, $max, $id_category = -1){
		if (!is_array($id_category))
        	$id_category = array($id_category);
        
        $id_category = implode(',', $id_category);
        
        $condition = '';
        
        if ($min == 'less')
        	$condition = $searchBy . ' <= ' . doubleval($max);
        else if ($max = 'greater')
        	$condition = $searchBy . ' >= ' . doubleval($min);
        else 
        	$condition = $searchBy . ' >= ' . doubleval($min) . ' AND ' .  $searchBy . ' <= ' . doubleval($max);
        	
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				 " WHERE  (id_pcategory IN (". intval($id_category). ") OR (-1 = " . intval($id_category) . ")) AND $condition";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	if($result)
			return $result[0]->num_record;
		else return 0;
	}
	
	function getAllBySource($offset, $number_record, $source = -1){
		$result = null;
		$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, vi_description " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE  id_source = " . intval($source) . " OR -1= " .intval($source) .
				" ORDER BY p.id_product DESC".
				" LIMIT $offset, $number_record";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	//Spreadsheet_Excel_Reader.
		return $result;
	}
	
	function countAllBySource($source = -1){
		$result = null;	
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				" WHERE  id_source = " . intval($source) . " OR -1= " .intval($source);
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result[0]->num_record;
	}
	
	function getAllByProvider($offset, $number_record, $source = -1){
        $name =  $this->lang_code. '_name';
        $description = $this->lang_code. '_description';
		$result = null;
		$sql = "SELECT p.*, p.$name name, u.vi_name unit, prov.".$name." provider, s.".$name." source, $description description " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
                " LEFT JOIN  gtb_provider prov ON prov.id_provider = p.id_provider" .
				" WHERE  p.id_provider = " . intval($source) . " OR -1= " .intval($source) .
				" ORDER BY p.id_product DESC".
				" LIMIT $offset, $number_record";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	//Spreadsheet_Excel_Reader.
		return $result;
	}
	
	function countAllByProvider($source = -1){
		$result = null;	
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				" WHERE  id_provider = " . intval($source) . " OR -1= " .intval($source);
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result[0]->num_record;
	}
	
	function getAllByIds($ids){
		$ids = implode(',', $ids);
		$result = null;
		$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, vi_description, image " .
				" FROM gtb_product p " .
				" INNER JOIN  gtb_unit u ON u.id = p.id_unit" .
				" INNER JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE id_product IN ($ids) " .
				" ORDER BY p.id_product DESC";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result;
	}
	
	function getAllByCodes($codes){
		$ids = implode(',', $codes);
		$result = null;
		$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, vi_description, image " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE p.code_pcategory IN ($ids) " .
				" ORDER BY p.id_product DESC";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	//echo $sql . '<br>' ;
		return $result;
	}
	
	function getByParentCode($code_parent){
		$result = null;
		$sql = "SELECT *
				FROM gtb_pcategory WHERE code_parent_pcategory = '".$this->db->escape($code_parent)."' ORDER BY id_pcategory DESC";
		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result;
	}
	
	function getMaxPosition($category_parent_id){
		
		$sql = "SELECT MAX(position) as position_max
				FROM category 
				WHERE category_parent_id = $category_parent_id";
		$query = $this->db->query($sql);
		if($result = $query->result()){
			return $result[0]->position_max;
		}
       
		return 0;
	}
	
	function getById($id){
		
		$sql = "SELECT p.*, u.vi_name unit, s.vi_name source, provider.vi_name provider
				  FROM gtb_product p".
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" LEFT JOIN  gtb_provider provider ON p.id_provider = p.id_provider" .
				" WHERE p.id_product = " . intval($id);
		$query = $this->db->query($sql);
		if($result = $query->result_array()){
			return $result[0];
		}

		return null;
	}
	
	function getByCode($code){
		
		$sql = "SELECT *
				FROM gtb_product 
				WHERE code = " . $this->db->escape($code);
		$query = $this->db->query($sql);
		if($result = $query->result_array()){
			return $result[0];
		}
       
		return null;
	}
	
	function getByLinkRewrite($link_rewrite){
		
		$sql = "SELECT *
				FROM gtb_pcategory 
				WHERE link_rewrite = " . $this->db->escape($link_rewrite);
		$query = $this->db->query($sql);
		if($result = $query->result()){
			return $result[0];
		}
       
		return null;
	}
	
	function update_code_category(){
		$sql = "UPDATE gtb_product p
				INNER JOIN gtb_pcategory c ON p.code_pcategory = c.code
				SET p.id_pcategory = c.id_pcategory";
		$this->db->query($sql);
		return $this->db->affected_rows(); 
	}
		
	function getSpecialProduct($number_record = 0){
		$result = null;
        if($number_record)
		$sql = "SELECT p.* " .
				" FROM gtb_product p " .
				" WHERE  special = 1".
				" ORDER BY date_upd DESC".
				" LIMIT 0, $number_record";
        else
            $sql = "SELECT p.* " .
				" FROM gtb_product p " .
				" WHERE  special = 1".
				" ORDER BY date_upd DESC";

		
		$query = $this->db->query($sql);
		$result = $query->result();
       	
		return $result;
	}

    function getBestSellProduct($number_record = 0){
		$result = null;
        if($number_record)
		$sql = "SELECT p.* " .
				" FROM gtb_product p " .
				" WHERE  best_sell = 1".
				" ORDER BY rand()".
				" LIMIT 0, $number_record";
        else
            $sql = "SELECT p.* " .
				" FROM gtb_product p " .
				" WHERE  best_sell = 1".
				" ORDER BY date_upd DESC";

		$query = $this->db->query($sql);
		$result = $query->result();

		return $result;
	}
	
	
	function getNewProduct($number_record = 20){
		$result = null;
        if($number_record)
		$sql = "SELECT p.* " .
				" FROM gtb_product p " .
				" WHERE  active = 1".
				" ORDER BY id_product DESC".
				" LIMIT 0, $number_record";
        
		$query = $this->db->query($sql);
		$result = $query->result_array();

		return $result;
	}
	
	
	function update_visit($id){
		$sql = "UPDATE gtb_product
				SET visited = visited + 1
				WHERE id_product = " . intval($id);
		$this->db->query($sql);
		return $this->db->affected_rows();
	}

    function getByCatProvider($offset, $number_record, $id_category, $id_provider){
		$result = null;
        $name =  $this->lang_code. '_name';
        $description = $this->lang_code. '_description';
		$sql = "SELECT p.*, p.$name name, u.$name unit, s.$name source, $description description, price " .
				" FROM gtb_product p " .
				" LEFT JOIN  gtb_unit u ON u.id = p.id_unit" .
				" LEFT JOIN  gtb_source s ON s.id = p.id_source" .
				" WHERE  id_pcategory = " . intval($id_category) . " AND p.id_provider= " .intval($id_provider) .
				" ORDER BY p.id_product DESC".
				" LIMIT $offset, $number_record";
       // echo '<br>'. $sql . '<br>';
		$query = $this->db->query($sql);
		$result = $query->result();
       	//Spreadsheet_Excel_Reader.
		return $result;
	}

	function countByCatProvider($id_category, $id_provider){
		$result = null;
		$sql = "SELECT COUNT(*) as num_record" .
				" FROM gtb_product " .
				" WHERE  id_pcategory = " . intval($id_category) . " AND id_provider= " .intval($id_provider) ;

		$query = $this->db->query($sql);
		$result = $query->result();

		return $result[0]->num_record;
	}

    function getFindByCategories($id_categories){
		$result = array();

        if(count($id_categories) == 0){
            return $result;
        }

		$sql = "SELECT p.id_product " .
				" FROM gtb_product p " .
				" WHERE  id_pcategory IN (". implode(',',$id_category). ")" .
				" ORDER BY p.id_product DESC";

		$query = $this->db->query($sql);
		$result = $query->result();

		return $result;
	}
	
function setImage($image, $newsID){
            $sql = "UPDATE gtb_product SET image = ". $this->db->escape($image).
                   " WHERE id_product = " . intval($newsID);
            $query = $this->db->query($sql);
            return $this->db->affected_rows();
        }
}

// EOF

